"""
modify parameters with one click
Author:lh
Date:2020-05-04
"""

import os

import pymysql
from sshtunnel import SSHTunnelForwarder

ssh_host = "218.77.58.6"
ssh_port = 10008
ssh_user = "user"
ssh_password = "pw122333"
mysql_host = "10.109.81.246"
mysql_user = "ljh"
mysql_password = "ljh123"
mysql_db="wfngindb_test"
mysql_port="3306"
server = SSHTunnelForwarder((ssh_host, ssh_port),
                                ssh_username=ssh_user,
                                ssh_password=ssh_password,
                                remote_bind_address=(mysql_host, 3306))
server.start()
mysql_port = (server.local_bind_port)
mysql_host = "localhost"

# pymysql
def get_db():
    coon = pymysql.connect(host=mysql_host, user=mysql_user, passwd=mysql_password, db=mysql_db, port=mysql_port)
    return coon
db=get_db()
def run(sql):
    c=db.cursor()
    try:
        c.execute(sql)
        print(sql, 'yes')
    except Exception as e:
        print(e,'执行失败')
        pass


sql="""
'set global auto_increment_increment=1;'
'set global auto_increment_offset=1;'
'set global autocommit="ON";'
'set global automatic_sp_privileges="ON";'
'set global avoid_temporal_upgrade="OFF";'
'set global back_log=80;'
'set global basedir="/usr/";'
'set global big_tables="OFF";'
'set global bind_address="*";'
'set global binlog_cache_size=32768;'
'set global binlog_checksum="CRC32";'
'set global binlog_direct_non_transactional_updates="OFF";'
'set global binlog_error_action="ABORT_SERVER";'
'set global binlog_format="ROW";'
'set global binlog_group_commit_sync_delay=0;'
'set global binlog_group_commit_sync_no_delay_count=0;'
'set global binlog_gtid_simple_recovery="ON";'
'set global binlog_max_flush_queue_time=0;'
'set global binlog_order_commits="ON";'
'set global binlog_row_image="FULL";'
'set global binlog_rows_query_log_events="OFF";'
'set global binlog_stmt_cache_size=32768;'
'set global block_encryption_mode="aes-128-ecb";'
'set global bulk_insert_buffer_size=125829120;'
'set global character_set_client="utf8mb4";'
'set global character_set_connection="utf8mb4";'
'set global character_set_database="latin1";'
'set global character_set_filesystem="binary";'
'set global character_set_results="utf8mb4";'
'set global character_set_server="latin1";'
'set global character_set_system="utf8";'
'set global character_sets_dir="/usr/share/mysql/charsets/";'
'set global check_proxy_users="OFF";'
'set global collation_connection="utf8mb4_general_ci";'
'set global collation_database="latin1_swedish_ci";'
'set global collation_server="latin1_swedish_ci";'
'set global completion_type="NO_CHAIN";'
'set global concurrent_insert="AUTO";'
'set global connect_timeout=10;'
'set global core_file="OFF";'
'set global datadir="/var/lib/mysql/";'
'set global date_format="%Y-%m-%d";'
'set global datetime_format="%Y-%m-%d %H:%i:%s";'
'set global default_authentication_plugin="mysql_native_password";'
'set global default_password_lifetime=0;'
'set global default_storage_engine="InnoDB";'
'set global default_tmp_storage_engine="InnoDB";'
'set global default_week_format=0;'
'set global delay_key_write="ON";'
'set global delayed_insert_limit=100;'
'set global delayed_insert_timeout=300;'
'set global delayed_queue_size=1000;'
'set global disconnect_on_expired_password="ON";'
'set global div_precision_increment=4;'
'set global end_markers_in_json="OFF";'
'set global enforce_gtid_consistency="OFF";'
'set global eq_range_index_dive_limit=200;'
'set global error_count=0;'
'set global event_scheduler="OFF";'
'set global expire_logs_days=0;'
'set global explicit_defaults_for_timestamp="OFF";'
'set global flush="OFF";'
'set global flush_time=0;'
'set global foreign_key_checks="ON";'
'set global ft_max_word_len=84;'
'set global ft_min_word_len=4;'
'set global ft_query_expansion_limit=20;'
'set global ft_stopword_file="(built-in)";'
'set global general_log="OFF";'
'set global general_log_file="/var/lib/mysql/coreserver-mysql-78b9f6cb8-9p9r7.log";'
'set global group_concat_max_len=1024;'
'set global gtid_executed_compression_period=1000;'
'set global gtid_mode="OFF";'
'set global gtid_next="AUTOMATIC";'
'set global have_compress="YES";'
'set global have_crypt="YES";'
'set global have_dynamic_loading="YES";'
'set global have_geometry="YES";'
'set global have_openssl="YES";'
'set global have_profiling="YES";'
'set global have_query_cache="YES";'
'set global have_rtree_keys="YES";'
'set global have_ssl="YES";'
'set global have_statement_timeout="YES";'
'set global have_symlink="DISABLED";'
'set global host_cache_size=279;'
'set global hostname="coreserver-mysql-78b9f6cb8-9p9r7";'
'set global identity=0;'
'set global ignore_builtin_innodb="OFF";'
'set global innodb_adaptive_flushing="ON";'
'set global innodb_adaptive_flushing_lwm=10;'
'set global innodb_adaptive_hash_index="ON";'
'set global innodb_adaptive_hash_index_parts=8;'
'set global innodb_adaptive_max_sleep_delay=150000;'
'set global innodb_api_bk_commit_interval=5;'
'set global innodb_api_disable_rowlock="OFF";'
'set global innodb_api_enable_binlog="OFF";'
'set global innodb_api_enable_mdl="OFF";'
'set global innodb_api_trx_level=0;'
'set global innodb_autoextend_increment=64;'
'set global innodb_autoinc_lock_mode=1;'
'set global innodb_buffer_pool_chunk_size=134217728;'
'set global innodb_buffer_pool_dump_at_shutdown="ON";'
'set global innodb_buffer_pool_dump_now="OFF";'
'set global innodb_buffer_pool_dump_pct=25;'
'set global innodb_buffer_pool_filename="ib_buffer_pool";'
'set global innodb_buffer_pool_instances=1;'
'set global innodb_buffer_pool_load_abort="OFF";'
'set global innodb_buffer_pool_load_at_startup="ON";'
'set global innodb_buffer_pool_load_now="OFF";'
'set global innodb_buffer_pool_size=7216192768;'
'set global innodb_change_buffer_max_size=25;'
'set global innodb_change_buffering="all";'
'set global innodb_checksum_algorithm="crc32";'
'set global innodb_checksums="ON";'
'set global innodb_cmp_per_index_enabled="OFF";'
'set global innodb_commit_concurrency=0;'
'set global innodb_compression_failure_threshold_pct=5;'
'set global innodb_compression_level=6;'
'set global innodb_compression_pad_pct_max=50;'
'set global innodb_concurrency_tickets=5000;'
'set global innodb_data_file_path="ibdata1:12M:autoextend";'
'set global innodb_deadlock_detect="ON";'
'set global innodb_default_row_format="dynamic";'
'set global innodb_disable_sort_file_cache="OFF";'
'set global innodb_doublewrite="ON";'
'set global innodb_fast_shutdown=1;'
'set global innodb_file_format="Barracuda";'
'set global innodb_file_format_check="ON";'
'set global innodb_file_format_max="Barracuda";'
'set global innodb_file_per_table="ON";'
'set global innodb_fill_factor=100;'
'set global innodb_flush_log_at_timeout=1;'
'set global innodb_flush_log_at_trx_commit=2;'
'set global innodb_flush_neighbors=1;'
'set global innodb_flush_sync="ON";'
'set global innodb_flushing_avg_loops=30;'
'set global innodb_force_load_corrupted="OFF";'
'set global innodb_force_recovery=0;'
'set global innodb_ft_cache_size=8000000;'
'set global innodb_ft_enable_diag_print="OFF";'
'set global innodb_ft_enable_stopword="ON";'
'set global innodb_ft_max_token_size=84;'
'set global innodb_ft_min_token_size=3;'
'set global innodb_ft_num_word_optimize=2000;'
'set global innodb_ft_result_cache_limit=2000000000;'
'set global innodb_ft_sort_pll_degree=2;'
'set global innodb_ft_total_cache_size=640000000;'
'set global innodb_io_capacity=200;'
'set global innodb_io_capacity_max=2000;'
'set global innodb_large_prefix="ON";'
'set global innodb_lock_wait_timeout=50;'
'set global innodb_locks_unsafe_for_binlog="OFF";'
'set global innodb_log_buffer_size=16777216;'
'set global innodb_log_checksums="ON";'
'set global innodb_log_compressed_pages="ON";'
'set global innodb_log_file_size=50331648;'
'set global innodb_log_files_in_group=2;'
'set global innodb_log_group_home_dir="./";'
'set global innodb_log_write_ahead_size=8192;'
'set global innodb_lru_scan_depth=1024;'
'set global innodb_max_dirty_pages_pct=75.000000;'
'set global innodb_max_dirty_pages_pct_lwm=0.000000;'
'set global innodb_max_purge_lag=0;'
'set global innodb_max_purge_lag_delay=0;'
'set global innodb_max_undo_log_size=1073741824;'
'set global innodb_numa_interleave="OFF";'
'set global innodb_old_blocks_pct=37;'
'set global innodb_old_blocks_time=1000;'
'set global innodb_online_alter_log_max_size=134217728;'
'set global innodb_open_files=2000;'
'set global innodb_optimize_fulltext_only="OFF";'
'set global innodb_page_cleaners=1;'
'set global innodb_page_size=16384;'
'set global innodb_print_all_deadlocks="OFF";'
'set global innodb_purge_batch_size=300;'
'set global innodb_purge_rseg_truncate_frequency=128;'
'set global innodb_purge_threads=4;'
'set global innodb_random_read_ahead="OFF";'
'set global innodb_read_ahead_threshold=56;'
'set global innodb_read_io_threads=4;'
'set global innodb_read_only="OFF";'
'set global innodb_replication_delay=0;'
'set global innodb_rollback_on_timeout="OFF";'
'set global innodb_rollback_segments=128;'
'set global innodb_sort_buffer_size=1048576;'
'set global innodb_spin_wait_delay=6;'
'set global innodb_stats_auto_recalc="ON";'
'set global innodb_stats_include_delete_marked="OFF";'
'set global innodb_stats_method="nulls_equal";'
'set global innodb_stats_on_metadata="OFF";'
'set global innodb_stats_persistent="ON";'
'set global innodb_stats_persistent_sample_pages=20;'
'set global innodb_stats_sample_pages=8;'
'set global innodb_stats_transient_sample_pages=8;'
'set global innodb_status_output="OFF";'
'set global innodb_status_output_locks="OFF";'
'set global innodb_strict_mode="ON";'
'set global innodb_support_xa="ON";'
'set global innodb_sync_array_size=1;'
'set global innodb_sync_spin_loops=30;'
'set global innodb_table_locks="ON";'
'set global innodb_temp_data_file_path="ibtmp1:12M:autoextend";'
'set global innodb_thread_concurrency=20;'
'set global innodb_thread_sleep_delay=0;'
'set global innodb_undo_directory="./";'
'set global innodb_undo_log_truncate="OFF";'
'set global innodb_undo_logs=128;'
'set global innodb_undo_tablespaces=0;'
'set global innodb_use_native_aio="ON";'
'set global innodb_version=5.7.20;'
'set global innodb_write_io_threads=4;'
'set global insert_id=0;'
'set global interactive_timeout=28800;'
'set global internal_tmp_disk_storage_engine="InnoDB";'
'set global join_buffer_size=262144;'
'set global keep_files_on_create="OFF";'
'set global key_buffer_size=536870912;'
'set global key_cache_age_threshold=300;'
'set global key_cache_block_size=1024;'
'set global key_cache_division_limit=100;'
'set global large_files_support="ON";'
'set global large_page_size=0;'
'set global large_pages="OFF";'
'set global last_insert_id=0;'
'set global lc_messages="en_US";'
'set global lc_messages_dir="/usr/share/mysql/";'
'set global lc_time_names="en_US";'
'set global license="GPL";'
'set global local_infile="ON";'
'set global lock_wait_timeout=31536000;'
'set global locked_in_memory="OFF";'
'set global log_bin="OFF";'
'set global log_bin_trust_function_creators="OFF";'
'set global log_bin_use_v1_row_events="OFF";'
'set global log_builtin_as_identified_by_password="OFF";'
'set global log_error="stderr";'
'set global log_error_verbosity=3;'
'set global log_output="FILE";'
'set global log_queries_not_using_indexes="OFF";'
'set global log_slave_updates="OFF";'
'set global log_slow_admin_statements="OFF";'
'set global log_slow_slave_statements="OFF";'
'set global log_statements_unsafe_for_binlog="ON";'
'set global log_syslog="OFF";'
'set global log_syslog_facility="daemon";'
'set global log_syslog_include_pid="ON";'
'set global log_throttle_queries_not_using_indexes=0;'
'set global log_timestamps="UTC";'
'set global log_warnings=2;'
'set global long_query_time=10.000000;'
'set global low_priority_updates="OFF";'
'set global lower_case_file_system="OFF";'
'set global lower_case_table_names=0;'
'set global master_info_repository="FILE";'
'set global master_verify_checksum="OFF";'
'set global max_allowed_packet=4194304;'
'set global max_binlog_cache_size=18446744073709547520;'
'set global max_binlog_size=1073741824;'
'set global max_binlog_stmt_cache_size=18446744073709547520;'
'set global max_connect_errors=1000000;'
'set global max_connections=100000;'
'set global max_delayed_threads=20;'
'set global max_digest_length=1024;'
'set global max_error_count=64;'
'set global max_execution_time=0;'
'set global max_heap_table_size=16777216;'
'set global max_insert_delayed_threads=20;'
'set global max_join_size=18446744073709551615;'
'set global max_length_for_sort_data=1024;'
'set global max_points_in_geometry=65536;'
'set global max_prepared_stmt_count=16382;'
'set global max_relay_log_size=0;'
'set global max_seeks_for_key=18446744073709551615;'
'set global max_sort_length=1024;'
'set global max_sp_recursion_depth=0;'
'set global max_tmp_tables=32;'
'set global max_user_connections=0;'
'set global max_write_lock_count=18446744073709551615;'
'set global metadata_locks_cache_size=1024;'
'set global metadata_locks_hash_instances=8;'
'set global min_examined_row_limit=0;'
'set global multi_range_count=256;'
'set global myisam_data_pointer_size=6;'
'set global myisam_max_sort_file_size=9223372036853727232;'
'set global myisam_mmap_size=18446744073709551615;'
'set global myisam_recover_options="OFF";'
'set global myisam_repair_threads=1;'
'set global myisam_sort_buffer_size=8388608;'
'set global myisam_stats_method="nulls_unequal";'
'set global myisam_use_mmap="OFF";'
'set global mysql_native_password_proxy_users="OFF";'
'set global net_buffer_length=16384;'
'set global net_read_timeout=30;'
'set global net_retry_count=10;'
'set global net_write_timeout=60;'
'set global new="OFF";'
'set global ngram_token_size=2;'
'set global offline_mode="OFF";'
'set global old="OFF";'
'set global old_alter_table="OFF";'
'set global old_passwords=0;'
'set global open_files_limit=1048576;'
'set global optimizer_prune_level=1;'
'set global optimizer_search_depth=62;'
'set global optimizer_switch="index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on";'
'set global optimizer_trace="enabled=off,one_line=off";'
'set global optimizer_trace_features="greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on";'
'set global optimizer_trace_limit=1;'
'set global optimizer_trace_max_mem_size=16384;'
'set global optimizer_trace_offset="-1";'
'set global parser_max_mem_size=18446744073709551615;'
'set global performance_schema="ON";'
'set global performance_schema_accounts_size="-1";'
'set global performance_schema_digests_size=10000;'
'set global performance_schema_events_stages_history_long_size=10000;'
'set global performance_schema_events_stages_history_size=10;'
'set global performance_schema_events_statements_history_long_size=10000;'
'set global performance_schema_events_statements_history_size=10;'
'set global performance_schema_events_transactions_history_long_size=10000;'
'set global performance_schema_events_transactions_history_size=10;'
'set global performance_schema_events_waits_history_long_size=10000;'
'set global performance_schema_events_waits_history_size=10;'
'set global performance_schema_hosts_size="-1";'
'set global performance_schema_max_cond_classes=80;'
'set global performance_schema_max_cond_instances="-1";'
'set global performance_schema_max_digest_length=1024;'
'set global performance_schema_max_file_classes=80;'
'set global performance_schema_max_file_handles=32768;'
'set global performance_schema_max_file_instances="-1";'
'set global performance_schema_max_index_stat="-1";'
'set global performance_schema_max_memory_classes=320;'
'set global performance_schema_max_metadata_locks="-1";'
'set global performance_schema_max_mutex_classes=210;'
'set global performance_schema_max_mutex_instances="-1";'
'set global performance_schema_max_prepared_statements_instances="-1";'
'set global performance_schema_max_program_instances="-1";'
'set global performance_schema_max_rwlock_classes=40;'
'set global performance_schema_max_rwlock_instances="-1";'
'set global performance_schema_max_socket_classes=10;'
'set global performance_schema_max_socket_instances="-1";'
'set global performance_schema_max_sql_text_length=1024;'
'set global performance_schema_max_stage_classes=150;'
'set global performance_schema_max_statement_classes=193;'
'set global performance_schema_max_statement_stack=10;'
'set global performance_schema_max_table_handles="-1";'
'set global performance_schema_max_table_instances="-1";'
'set global performance_schema_max_table_lock_stat="-1";'
'set global performance_schema_max_thread_classes=50;'
'set global performance_schema_max_thread_instances="-1";'
'set global performance_schema_session_connect_attrs_size=512;'
'set global performance_schema_setup_actors_size="-1";'
'set global performance_schema_setup_objects_size="-1";'
'set global performance_schema_users_size="-1";'
'set global pid_file="/var/run/mysqld/mysqld.pid";'
'set global plugin_dir="/usr/lib/mysql/plugin/";'
'set global port=3306;'
'set global preload_buffer_size=32768;'
'set global profiling="ON";'
'set global profiling_history_size=15;'
'set global protocol_version=10;'
'set global pseudo_slave_mode="OFF";'
'set global pseudo_thread_id=35344;'
'set global query_alloc_block_size=8192;'
'set global query_cache_limit=1048576;'
'set global query_cache_min_res_unit=4096;'
'set global query_cache_size=67108864;'
'set global query_cache_type="OFF";'
'set global query_cache_wlock_invalidate="OFF";'
'set global query_prealloc_size=8192;'
'set global rand_seed1=0;'
'set global rand_seed2=0;'
'set global range_alloc_block_size=4096;'
'set global range_optimizer_max_mem_size=8388608;'
'set global rbr_exec_mode="STRICT";'
'set global read_buffer_size=4194304;'
'set global read_only="OFF";'
'set global read_rnd_buffer_size=16777216;'
'set global relay_log_basename="/var/lib/mysql/coreserver-mysql-78b9f6cb8-9p9r7-relay-bin";'
'set global relay_log_index="/var/lib/mysql/coreserver-mysql-78b9f6cb8-9p9r7-relay-bin.index";'
'set global relay_log_info_file="relay-log.info";'
'set global relay_log_info_repository="FILE";'
'set global relay_log_purge="ON";'
'set global relay_log_recovery="OFF";'
'set global relay_log_space_limit=0;'
'set global report_port=3306;'
'set global require_secure_transport="OFF";'
'set global rpl_stop_slave_timeout=31536000;'
'set global secure_auth="ON";'
'set global secure_file_priv="/var/lib/mysql-files/";'
'set global server_id=0;'
'set global server_id_bits=32;'
'set global server_uuid=57bd8716-254d-11ea-971f-365a6f66245c;'
'set global session_track_gtids="OFF";'
'set global session_track_schema="ON";'
'set global session_track_state_change="OFF";'
'set global session_track_system_variables="time_zone,autocommit,character_set_client,character_set_results,character_set_connection";'
'set global session_track_transaction_info="OFF";'
'set global sha256_password_proxy_users="OFF";'
'set global show_compatibility_56="OFF";'
'set global show_old_temporals="OFF";'
'set global skip_external_locking="ON";'
'set global skip_name_resolve="ON";'
'set global skip_networking="OFF";'
'set global skip_show_database="OFF";'
'set global slave_allow_batching="OFF";'
'set global slave_checkpoint_group=512;'
'set global slave_checkpoint_period=300;'
'set global slave_compressed_protocol="OFF";'
'set global slave_exec_mode="STRICT";'
'set global slave_load_tmpdir="/tmp";'
'set global slave_max_allowed_packet=1073741824;'
'set global slave_net_timeout=60;'
'set global slave_parallel_type="DATABASE";'
'set global slave_parallel_workers=0;'
'set global slave_pending_jobs_size_max=16777216;'
'set global slave_preserve_commit_order="OFF";'
'set global slave_rows_search_algorithms="TABLE_SCAN,INDEX_SCAN";'
'set global slave_skip_errors="OFF";'
'set global slave_sql_verify_checksum="ON";'
'set global slave_transaction_retries=10;'
'set global slow_launch_time=5;'
'set global slow_query_log="OFF";'
'set global slow_query_log_file="/var/lib/mysql/coreserver-mysql-78b9f6cb8-9p9r7-slow.log";'
'set global socket="/var/run/mysqld/mysqld.sock";'
'set global sort_buffer_size=33554432;'
'set global sql_auto_is_null="OFF";'
'set global sql_big_selects="ON";'
'set global sql_buffer_result="OFF";'
'set global sql_log_bin="ON";'
'set global sql_log_off="OFF";'
'set global sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";'
'set global sql_notes="ON";'
'set global sql_quote_show_create="ON";'
'set global sql_safe_updates="OFF";'
'set global sql_select_limit=18446744073709551615;'
'set global sql_slave_skip_counter=0;'
'set global sql_warnings="OFF";'
'set global ssl_ca="ca.pem";'
'set global ssl_cert="server-cert.pem";'
'set global ssl_key="server-key.pem";'
'set global stored_program_cache=256;'
'set global super_read_only="OFF";'
'set global sync_binlog=1;'
'set global sync_frm="ON";'
'set global sync_master_info=10000;'
'set global sync_relay_log=10000;'
'set global sync_relay_log_info=10000;'
'set global system_time_zone="CST";'
'set global table_definition_cache=1400;'
'set global table_open_cache=2000;'
'set global table_open_cache_instances=16;'
'set global thread_cache_size=16384;'
'set global thread_handling="one-thread-per-connection";'
'set global thread_stack=262144;'
'set global time_format="%H:%i:%s";'
'set global time_zone="SYSTEM";'
'set global timestamp=1583313176.535401;'
'set global tls_version="TLSv1,TLSv1.1";'
'set global tmp_table_size=268435456;'
'set global tmpdir="/tmp";'
'set global transaction_alloc_block_size=8192;'
'set global transaction_allow_batching="OFF";'
'set global transaction_isolation="REPEATABLE-READ";'
'set global transaction_prealloc_size=4096;'
'set global transaction_read_only="OFF";'
'set global transaction_write_set_extraction="OFF";'
'set global tx_isolation="REPEATABLE-READ";'
'set global tx_read_only="OFF";'
'set global unique_checks="ON";'
'set global updatable_views_with_limit="YES";'
'set global version="5.7.20";'
'set global version_comment="MySQL Community Server (GPL)";'
'set global version_compile_machine="x86_64";'
'set global version_compile_os="Linux";'
'set global wait_timeout=28800;'
'set global warning_count=0;'
"""
for i in sql.split(';'):
    if len(i)>2:
        sqltemp=i[3:]+';'
        run(sqltemp)
db.commit()